import sqlite3,os
from support.ui.console import Log
from support.utils.file.base import MediaFile

class DBHelper(MediaFile):
    TABLE_ALBUM = "album"
    TABLE_PICTURE = "picture"
    ID = 'Id'
    NAME = 'Name'
    HASH = 'Hash'
    LOCAL = 'Local'
    WIDTH = 'Width'
    HEIGHT = 'Height'
    DATE = 'Date'

    def __createTable(self):
        cur = self.conn.cursor()
        sql = 'CREATE TABLE IF NOT EXISTS {}({} INTEGER PRIMARY KEY AUTOINCREMENT, {} TEXT, {} TEXT);'.format(self.TABLE_ALBUM, self.ID, self.NAME, self.HASH)
        cur.execute(sql)
        sql = 'CREATE TABLE IF NOT EXISTS {}({} INTEGER, {} TEXT, {} NUMBER, {} NUMBER, {} NUMBER);'.format(self.TABLE_PICTURE, self.ID, self.LOCAL, self.WIDTH, self.HEIGHT, self.DATE)
        cur.execute(sql)
        self.conn.commit()
        cur.close()
        
    def __init__(self, path, dbname):
        self.createPathIfNotExists(path)
        self.conn = sqlite3.connect(os.path.join(path,dbname))
        self.__createTable()

    def __loadQueryResult(self, table, result:list):
        array = []
        for row in result:
            ret = {}
            if(table == self.TABLE_ALBUM):
                ret[self.ID] = row[0]
                ret[self.NAME] = row[1]
                ret[self.HASH] = row[2]
            elif(table == self.TABLE_PICTURE):
                ret[self.ID] = row[0]
                ret[self.LOCAL] = row[1]
                ret[self.WIDTH] = row[2]
                ret[self.HEIGHT] = row[3]
                ret[self.DATE] = row[4]
            array.append(ret)
        return array

    def __transListToStr(self, lst:list):
        return '"' + '","'.join(list(map(lambda x:str(x),lst))) + '"'
        
    def close(self):
        self.conn.close()

    def execute(self, sql):
        print(sql)
        cur = self.conn.cursor()
        cur.execute(sql)
        ret = cur.fetchall()
        print(ret)
        cur.close()
        return ret
        
    def query(self, table, column, value, condition = None):
        sql = 'SELECT * FROM {0} WHERE {1} {3} "{2}";'.format(table, column, value, '=' if condition is None else condition)
        cur = self.conn.cursor()
        cur.execute(sql)
        ret = self.__loadQueryResult(table, cur.fetchall())
        cur.close()
        return ret
        
    def insert(self, table, valueMap:dict):
        sql = 'INSERT INTO {0}({1}) VALUES({2});'.format(table, self.__transListToStr(valueMap.keys()), self.__transListToStr(valueMap.values()))
        cur = self.conn.cursor()
        cur.execute(sql)
        id = cur.lastrowid
        self.conn.commit()
        cur.close()
        return id
    
    def delete(self, table, column, value, condition = None):
        sql = 'DELETE FROM {0} WHERE {1} {3} {2};'.format(table, column, value, '=' if condition is None else condition)
        cur = self.conn.cursor()
        cur.execute(sql)
        self.conn.commit()
        cur.close()
        
    def change(self, table, column, value, valueMap:dict, condition = None):
        newValue = ''
        for k, v in valueMap:
            newValue += ', {}="{}"'.format(k,v)
        newValue = newValue.strip(',')
        sql = 'UPDATE {0} SET {4} WHERE {1} {3} {2};'.format(table, column, value,  '=' if condition is None else condition, newValue)
        cur = self.conn.cursor()
        cur.execute(sql)
        self.conn.commit()
        cur.close()
